PostgreSQL postgresqltuner
1 背景知识
postgresqltuner 工具用于分析 PostgreSQL 服务器并给出建议，它是受到了 MySQLTuner-perl 的启发，它是perl脚本写的。
这个软件使用起来非常简单，直接下载解压，执行脚本就行了。
2 手动安装
2.1 操作系统准备
dnf install perl-DBD-Pg perl-DBI perl-Term-ANSIColor perl-Memoize-y
2.2 调用下载脚本命令
Tip
选择以下命令之一即可。
su - postgres
wget -O postgresqltuner.pl postgresqltuner.pl
wget -O postgresqltuner.pl https://postgresqltuner.pl
curl -Lo postgresqltuner.pl postgresqltuner.pl
curl -Lo postgresqltuner.pl https://postgresqltuner.pl
2.3 更改脚本权限
chmod +x postgresqltuner.pl
3 RPM安装
- 在 RHEL/CentOS 需要开启 EPEL。
- 安装postgresqltuner 工具。
dnf install postgresqltuner
4 本机环境评估
4.1 用户名密码连接
./postgresqltuner.pl --host=localhost --database=testdb --user=postgres --password=postgres
postgresqltuner.pl version 1.0.1
[OK] I can invoke executables
Connecting to localhost:5432 database testdb as user 'postgres'...
[OK] The user account used by me for reporting has superuser rights on this PostgreSQL instance
===== OS information =====
[INFO] OS: linux Version: 4.18.0-425.3.1.el8.x86_64 Arch: x86_64-linux-thread-multi
[INFO] OS total memory: 7.51 GB
[OK] vm.overcommit_memory is adequate: no memory overcommitment
[INFO] Running under a vmware hypervisor
[INFO] Currently used I/O scheduler(s): mq-deadline
[WARN] If PostgreSQL runs in a virtual machine, I cannot know the underlying physical storage type. Use the --ssd arg if the VM only uses SSD storage
===== General instance informations =====
----- PostgreSQL version -----
[OK] You are using the latest PostreSQL major version (16.0)
----- Uptime -----
[INFO] Service uptime: 1d 53m 27s
----- Databases -----
[INFO] Database count (except templates): 2
[INFO] Database list (except templates): postgres testdb
----- Extensions -----
[INFO] Number of activated extensions: 1
[INFO] Activated extensions: plpgsql
[WARN] Extension pg_stat_statements is disabled in database testdb
----- Users -----
[OK] No user account will expire in less than 7 days
[OK] No user with password=username
[OK] Password encryption enabled
----- Connection information -----
[INFO] max_connections: 100
[INFO] Current used connections: 6 (6.00%)
[INFO] 3 connections are reserved for super user (3.00%)
[INFO] Average connection age: 20h 44m 33s
----- Memory usage -----
[INFO] Configured work_mem: 4.00 MB
[INFO] Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO] Total work_mem (per connection): 6.00 MB
[INFO] shared_buffers: 128.00 MB
[INFO] Track activity reserved size: 0.00 B
[WARN] maintenance_work_mem is less or equal to its default value. Increase it to reduce maintenance tasks duration
[INFO] Max memory usage:
shared_buffers (128.00 MB)
+ max_connections * work_mem * average_work_mem_buffers_per_connection (100 * 4.00 MB * 150 / 100 = 600.00 MB)
+ autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
+ track activity size (0.00 B)
= 920.00 MB
[INFO] effective_cache_size: 4.00 GB
[INFO] Cumulated size of all databases: 37.51 MB
[WARN] shared_buffer is too big for the total databases size, uselessly using memory
[INFO] PostgreSQL maximum amount of memory used: 11.97% of system RAM
[INFO] PostgreSQL will not use more than 60% of the amount of RAM. On a dedicated host you may increase PostgreSQL shared_buffers, as it may improve performance
[INFO] max memory usage + effective_cache_size - shared_buffers is 63.59% of the amount of RAM
----- Huge Pages -----
[WARN] No Huge Pages available on the system
----- Logs -----
[OK] log_hostname is off: no reverse DNS lookup latency
[WARN] Log of long queries deactivated. It will be more difficult to optimize query performance
[BAD] log_statement=all is very storage-intensive and only usefull for debuging
----- Two-phase commit -----
[OK] Currently there is no two-phase commit transaction
----- Autovacuum -----
[OK] autovacuum is activated
[INFO] autovacuum_max_workers: 3
----- Checkpoint -----
[OK] checkpoint_completion_target (0.9) OK
[INFO] Given those settings PostgreSQL may (depending on its workload) ask the kernel to write (to the storage) up to 1024.00 MB in a timeframe lasting 270 seconds <=> 3.79 MB bytes/second during this timeframe. You may want to check that your storage is able to cope with this, along with all other I/O (non-writing queries, other software...) operations potentially active during this timeframe. If this seems inadequate check max_wal_size, checkpoint_timeout and checkpoint_completion_target
----- Storage -----
[OK] fsync is on
[OK] synchronize_seqscans is on
----- WAL -----
----- Planner -----
[OK] I/O cost settings are set at their default values
[BAD] Some plan features are disabled: enable_partitionwise_aggregate,enable_partitionwise_join
===== Database information for database testdb =====
----- Database size -----
[INFO] Database testdb total size: 15.98 MB
[INFO] Database testdb tables size: 9.24 MB (57.85%)
[INFO] Database testdb indexes size: 6.73 MB (42.15%)
----- Tablespace location -----
[OK] No tablespace in PGDATA
----- Shared buffer hit rate -----
[INFO] shared_buffer_heap_hit_rate: 99.86%
[INFO] shared_buffer_toast_hit_rate: 90.90%
[INFO] shared_buffer_tidx_hit_rate: 99.14%
[INFO] shared_buffer_idx_hit_rate: 99.91%
[OK] This is very good (if this PostgreSQL instance was recently used as it usually is, and was not stopped since)
----- Indexes -----
[OK] No invalid index
[WARN] 40 indexes were not used since the last statistics run
----- Procedures -----
[WARN] 9 user procedures do not have custom cost and rows settings
===== Configuration advice =====
----- extension -----
[LOW] Enable pg_stat_statements in database testdb to collect statistics on all queries (not only those longer than log_min_duration_statement)
----- index -----
[MEDIUM] You have unused indexes in the database since the last statistics run. Please remove them if they are rarely or not used
----- proc -----
[LOW] You have custom procedures with default cost and rows setting. Reconfigure them with specific values to help the planner
----- storage -----
[HIGH] Use the --ssd arg if PostgreSQL only uses a SSD storage
4.2 套接字连接
./postgresqltuner.pl --host=/tmp
4.3 .pgpass 免密连接
为了提高安全性，请使用 .pgpass 密码文件，这样将不会在 SHELL 历史记录保存密码，也不会在启动的进程名称中显示密码。
- 编辑 .pgpass 文件。
vi ~/.pgpass
*:5432:*:postgres:postgres
chmod 600 ~/.pgpass
- 链接到服务器和数据库进行评估
./postgresqltuner.pl --host=localhost --database=testdb --user=postgres
5 使用Docker 进行环境评估
6 远程环境评估
使用 postgresqltuner 评估远程的 PostgreSQL 实例时，可能也会通过 SSH 收集操作系统信息。这时你需要配置 SSH 私钥方式远程连接到数据库主机。
请参考 Linux SSH 配置节点互信将两台主机配置为互信状态，并指定私钥路径。
Tip
如果私钥配置在默认目录下则 IdentityFile=~/.ssh/id_rsa 可以省略。
./postgresqltuner.pl --sshopt=Port=22 --sshopt=IdentityFile=~/.ssh/id_rsa --host=192.168.10.159 --database=testdb --user=postgres
7 工具选项
7.1 --wmp
一个复杂的查询可能会使用很多 work_mem 缓冲区，你可以配置每个连接的平均缓冲区的数量（单位百分比）。
--wmp 30
此参数默认值为：150%。
7.2 SSD 存储
如果 PostgreSQL 实例运行在虚拟机中，可能无法准确的检测到底层的磁盘类型。这时需要手工指定。
--ssd
7.3 --nocolor
输出的报告不会高亮，这对重定向保存评估文档非常重要。
7.4 --skip-ssh
当 PostgreSQL 在 RDS 上运行，无法使用 ssh 链接服务器。这将导致无法获取到准确的物理机的总体内存。在这种情况下，可以使用参数手动指定内存大小。
--skip-ssh --memory=8219082752